package com.kcs.fxmargin.dao;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.kcs.common.utils.Constant;
import com.kcs.common.utils.commonUtils;
import com.kcs.fxmargin.bo.DailyReportBO;
import com.kcs.fxmargin.model.TDealsSum;

public class DealsSumDao {
	private Logger log = Logger.getLogger(Constant.LOG_FX);

	public TDealsSum checkData(Connection conn, PreparedStatement pstm, ResultSet rs) throws Exception {
		TDealsSum dealsSum	= null;
		StringBuffer sql 	= new StringBuffer();
		
		sql.append(" SELECT * ");
		sql.append(" FROM t_deals_sum ");
		sql.append(" WHERE as_of_date = CAST(getdate() as DATE)  ");
		
		log.debug("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		
		rs = pstm.executeQuery();
		
		while(rs.next()){
			dealsSum = new TDealsSum();
			dealsSum.setId(rs.getString("id"));
			dealsSum.setAsOfDate(rs.getDate("as_of_date"));
			dealsSum.setCifNo(rs.getString("cif_no"));
			dealsSum.setCustLimit(rs.getBigDecimal("cust_limit"));
			dealsSum.setCashBal(rs.getBigDecimal("cash_bal"));
			dealsSum.setRealizedPl(rs.getBigDecimal("realized_pl"));
			dealsSum.setUnrealizedPl(rs.getBigDecimal("unrealized_pl"));
			dealsSum.setEquityBal(rs.getBigDecimal("equity_bal"));
			dealsSum.setInitialMargin(rs.getBigDecimal("initial_margin"));
			dealsSum.setExcessAmt(rs.getBigDecimal("excess_amt"));
			dealsSum.setMtnMargin(rs.getBigDecimal("mtn_margin"));
			dealsSum.setMarginCall(rs.getBigDecimal("margin_call"));
			dealsSum.setMarginLimit(rs.getBigDecimal("margin_limit"));
			dealsSum.setActiveStatus(rs.getString("active_status"));
			dealsSum.setCreateby(rs.getString("create_by"));
			dealsSum.setCreateDate(rs.getDate("create_dt"));
			dealsSum.setUpdateBy(rs.getString("update_by"));
			dealsSum.setUpdateDate(rs.getDate("update_dt"));
			dealsSum.setRowVersion(rs.getInt("row_version"));
		}
		
		return dealsSum;
	}
	
	public TDealsSum search(Connection conn, PreparedStatement pstm, ResultSet rs, String cifNo, String asOfDate) throws Exception {
		
		TDealsSum dealsSum	= null;
		StringBuffer sql 	= new StringBuffer();
		
		sql.append(" SELECT  id, ");
		sql.append(" 	as_of_date, ");
		sql.append(" 	cif_no, ");
		sql.append(" 	cust_limit, ");
		sql.append(" 	cash_bal, ");
		sql.append(" 	realized_pl, ");
		sql.append(" 	unrealized_pl, ");
		sql.append(" 	equity_bal, ");
		sql.append(" 	initial_margin , ");
		sql.append(" 	excess_amt, ");
		sql.append(" 	mtn_margin, ");
		sql.append(" 	margin_call, ");
		sql.append(" 	margin_limit, ");
		sql.append(" 	active_status, ");
		sql.append(" 	create_by, ");
		sql.append(" 	create_dt, ");
		sql.append(" 	update_by, ");
		sql.append(" 	update_dt , ");
		sql.append(" 	row_version ");
		sql.append(" FROM t_deals_sum ");
		sql.append(" WHERE cif_no = ? ");
		sql.append(" 	AND as_of_date = ? ");
		sql.append(" 	AND active_status='A' ");
		
		log.debug("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, cifNo);
		pstm.setDate(2, commonUtils.convertStringtoSQLDate(asOfDate, "dd/MM/yyyy"));
		
		rs = pstm.executeQuery();
		
		while(rs.next()){
			dealsSum = new TDealsSum();
			dealsSum.setId(rs.getString("id"));
			dealsSum.setAsOfDate(rs.getDate("as_of_date"));
			dealsSum.setCifNo(rs.getString("cif_no"));
			dealsSum.setCustLimit(rs.getBigDecimal("cust_limit"));
			dealsSum.setCashBal(rs.getBigDecimal("cash_bal"));
			dealsSum.setRealizedPl(rs.getBigDecimal("realized_pl"));
			dealsSum.setUnrealizedPl(rs.getBigDecimal("unrealized_pl"));
			dealsSum.setEquityBal(rs.getBigDecimal("equity_bal"));
			dealsSum.setInitialMargin(rs.getBigDecimal("initial_margin"));
			dealsSum.setExcessAmt(rs.getBigDecimal("excess_amt"));
			dealsSum.setMtnMargin(rs.getBigDecimal("mtn_margin"));
			dealsSum.setMarginCall(rs.getBigDecimal("margin_call"));
			dealsSum.setMarginLimit(rs.getBigDecimal("margin_limit"));
			dealsSum.setActiveStatus(rs.getString("active_status"));
			dealsSum.setCreateby(rs.getString("create_by"));
			dealsSum.setCreateDate(rs.getDate("create_dt"));
			dealsSum.setUpdateBy(rs.getString("update_by"));
			dealsSum.setUpdateDate(rs.getDate("update_dt"));
			dealsSum.setRowVersion(rs.getInt("row_version"));
		}
		
		return dealsSum;
	}
	
	public BigDecimal getCashBalance(Connection conn, 
			PreparedStatement pstm, 
			ResultSet rs, 
			String cifNo,
			String asOfDate) throws Exception {
		
		BigDecimal cashBalance 	= new BigDecimal(0);
		StringBuffer sql 		= new StringBuffer();
		
		sql.append(" WITH param AS ");
		sql.append("   (SELECT ? cust_info_id, ");
		sql.append("             ? as_of_date) ");
		sql.append(" SELECT ( ");
		sql.append(" SELECT ISNULL( ");
		sql.append("                 ( SELECT pos.outstd_value + pos.accrued_interest ");
		sql.append("                  FROM t_acc_pos pos, param p ");
		sql.append("                  WHERE pos.active_status='A' ");
		sql.append("                    AND pos.margin_acc_no = ");
		sql.append("                      (SELECT margin_acc_no ");
		sql.append("                       FROM m_cust_info ");
		sql.append("                       WHERE id=p.cust_info_id) ),0) + ");
		sql.append("   ( SELECT ISNULL(sum(a.amount),0) ");
		sql.append("    FROM t_acc_online a, ");
		sql.append("         param p ");
		sql.append("    WHERE a.active_status='A' ");
		sql.append("      AND a.cust_info_id = p.cust_info_id ");
		sql.append("      AND a.as_of_date = p.as_of_date ");
		sql.append("      AND a.type='D' ");
		sql.append("      AND NOT EXISTS ");
		sql.append("        ( SELECT 1 ");
		sql.append("         FROM t_acc_tran t, ");
		sql.append("              param p ");
		sql.append("         WHERE t.active_status='A' ");
		sql.append("           AND CAST(t.seq AS VARCHAR) = a.ref_no ");
		sql.append("           AND t.trans_date = p.as_of_date ");
		sql.append("           AND t.type='D' ) ) - ( ");
		sql.append(" SELECT ISNULL(sum(b.amount), 0) ");
		sql.append(" FROM t_acc_online b, ");
		sql.append("      param p ");
		sql.append(" WHERE b.active_status='A' ");
		sql.append("   AND b.cust_info_id = p.cust_info_id ");
		sql.append("   AND (p.as_of_date BETWEEN b.as_of_date AND b.value_date) ");
		sql.append("   AND b.type='W') ");
		sql.append(" ) as cash_balance ");
		
		log.debug("SQL -> " + sql.toString());
		
		pstm = conn.prepareStatement(sql.toString());
		pstm.setString(1, cifNo);
		pstm.setDate(2, commonUtils.convertStringtoSQLDate(asOfDate, "dd/MM/yyyy"));
		
		rs = pstm.executeQuery();
		
		while(rs.next()){
			cashBalance = rs.getBigDecimal("cash_balance");
		}
		
		log.debug("Cash Balance = " + cashBalance);
		
		return cashBalance;
	}
	
	public void callSPCallMarginLimit(Connection conn, CallableStatement cstmt, String cifNo, String asOfDate) throws Exception {
		StringBuffer sql = new StringBuffer();
		
		sql.append("{call SPCalMarginLimit(?,?,?)}");
		log.debug("SQL -> " + sql.toString());
		
		cstmt = conn.prepareCall(sql.toString());
		cstmt.setString(1, cifNo);
		cstmt.setDate(2, commonUtils.convertStringtoSQLDate(asOfDate, "dd/MM/yyyy"));
		cstmt.registerOutParameter(3, Types.NUMERIC);
		cstmt.execute();
	}
	
	public List<DailyReportBO> searchDailyReport(Connection conn, PreparedStatement pstm , ResultSet rs, DailyReportBO bo) throws Exception {
				
		StringBuffer sql 					= new StringBuffer();
		List<DailyReportBO> dailyReportList = new ArrayList<DailyReportBO>();
		DailyReportBO dailyReportBo			= null;

		sql.append(" SELECT s.cif_no, ");
		sql.append("        c.cust_name, ");
		sql.append("        s.cash_bal, ");
		sql.append("        s.realized_pl, ");
		sql.append("        s.unrealized_pl, ");
		sql.append("        s.equity_bal, ");
		sql.append("        s.initial_margin, ");
		sql.append("        s.excess_amt , ");
		sql.append("        s.mtn_margin, ");
		sql.append("        s.margin_call, ");
		sql.append("        s.margin_limit , ");
		sql.append("        CASE ");
		sql.append("            WHEN (s.margin_limit<0) THEN 'Over' ");
		sql.append("            ELSE 'Normal' ");
		sql.append("        END status ");
		sql.append(" FROM t_deals_sum s, ");
		sql.append("      m_cust_info c ");
		sql.append(" WHERE s.cif_no = c.cif_no  ");
		
		if (bo.getCifNo() != null && !"".equals(bo.getCifNo())) 		sql.append(" AND s.cif_no = ? ");
		
		if (bo.getAsOfDate() != null && !"".equals(bo.getAsOfDate())) 	sql.append(" AND s.as_of_date = ? ");
		
		if (bo.getStatus() != null && "N".equals(bo.getStatus())) 		sql.append(" AND s.margin_limit >= 0  ");
		else if (bo.getStatus() != null && "O".equals(bo.getStatus()))	sql.append(" AND s.margin_limit < 0 "); 
		
		sql.append(" ORDER BY cif_no ");
		
		log.debug("SQL -> " + sql.toString());
		int index = 1;
		
		pstm = conn.prepareStatement(sql.toString());
		
		if (bo.getCifNo() != null && !"".equals(bo.getCifNo())) {
			pstm.setString(index, bo.getCifNo());
			index++;
		}
		
		if (bo.getAsOfDate() != null && !"".equals(bo.getAsOfDate())) {
			pstm.setDate(index, bo.getAsOfDate());
			index++;
		}
		
		rs = pstm.executeQuery();
		index = 1;
		
		while(rs.next()) {
			dailyReportBo = new DailyReportBO();
			
			dailyReportBo.setRowNum(index);
			dailyReportBo.setCifNo(rs.getString("cif_no"));
			dailyReportBo.setCustomerName(rs.getString("cust_name"));
			dailyReportBo.setCashBalance(rs.getBigDecimal("cash_bal"));
			dailyReportBo.setRealizedPL(rs.getBigDecimal("realized_pl"));
			dailyReportBo.setUnRealzedPL(rs.getBigDecimal("unrealized_pl"));
			dailyReportBo.setEquityBalance(rs.getBigDecimal("equity_bal"));
			dailyReportBo.setImRequired(rs.getBigDecimal("initial_margin"));
			dailyReportBo.setExcess(rs.getBigDecimal("excess_amt"));
			dailyReportBo.setMmRequired(rs.getBigDecimal("mtn_margin"));
			dailyReportBo.setMarginCall(rs.getBigDecimal("margin_call"));
			dailyReportBo.setLimit(rs.getBigDecimal("margin_limit"));
			dailyReportBo.setStatus(rs.getString("status"));
			
			index++;
			dailyReportList.add(dailyReportBo);
		}
		
		log.debug("dailyReportList size = " + dailyReportList.size());
		
		return dailyReportList;
	}
}
